package com.ztesoft.web.checkup.service.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.stereotype.Service;

@Service
public class CheckUpByProcTransable {

	/**
	 * 执行full_backup_time_variant存储过程
	 * @param jdbcTemplate
	 * @param srcTable 源表基表名
	 * @param trgTable 目标基表名
	 * @param param 表名参数
	 * @return
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public String execProcTransable(DataSource dataSource,
			final String srcTable, final String trgTable, final String param) {
		return (String) new JdbcTemplate(dataSource).execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				String storedProc = "call full_backup_transable_variant(?,?,?,?)";// 调用的sql
				CallableStatement cs = con.prepareCall(storedProc);
				cs.setString(1, srcTable);
				cs.setString(2, trgTable);
				cs.setString(3, param);
				cs.registerOutParameter(4, Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				cs.execute();
				return cs.getString(4);
			}
		});
	}
	

	public String execNormalProcTransable(DataSource dataSource,
			final String srcTable, final String trgTable, final String param) throws Exception{
		Connection conn = null;
		CallableStatement cstmt = null;
		ResultSet rs = null;
		try {
			// 可能出现问题，表不存在，rename完无法重建表，重建表权限不足 user_tables表名大写问题
			conn = dataSource.getConnection();
			cstmt = conn.prepareCall("call full_backup_transable_variant(?, ?, ?)");
			cstmt.setString(1, srcTable);
			cstmt.setString(2, trgTable);
			cstmt.setString(3, param);
			rs = cstmt.executeQuery();
			rs.next();
			return rs.getString(1);
		} finally {
			JdbcUtils.closeStatement(cstmt);
			JdbcUtils.closeResultSet(rs);
			DataSourceUtils.releaseConnection(conn, dataSource);
		}
	}
	
}
